package com.dwxt.sport.service;

import com.dwxt.common.base.BaseResult;
import com.dwxt.common.base.IntegralCategory;
import com.dwxt.common.util.DateUtils;
import com.dwxt.common.util.OkHttpUtils;
import com.dwxt.sport.dao.AnalysisMapper;
import com.dwxt.sport.dao.DeviceSceneMapper;
import com.dwxt.sport.dao.PeopleFlowMapper;
import com.dwxt.sport.dao.WifeDeviceMapper;
import com.dwxt.sport.entity.*;
import com.dwxt.sport.util.ShuWeiUtil;
import io.swagger.models.auth.In;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import tk.mybatis.mapper.entity.Example;

import java.sql.Date;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class AnalysisService {
    @Autowired
    private AnalysisMapper analysisMapper;

    @Autowired
    private PeopleFlowMapper peopleFlowMapper;

    @Autowired
    private SceneService sceneService;

    @Autowired
    private DeviceSceneMapper deviceSceneMapper;

    @Autowired
    private WifeDeviceMapper wifeDeviceMapper;

    @Value("${app.url}")
    private String appUrl;

    @Value("${db.ip}")
    private String dbUrl;


    public List<Map> selectAllScene(Integer year, Integer month) {
        year = year == null ? DateUtils.getYear(null) : year;
        month = month == null ? DateUtils.getMonth(null) : month;
        List<PeopleFlow> peopleFlowList = selectAllPeoPleFlowByDate(year, month);
        List<Map> mapList = new ArrayList<>();
        String name = "全区";
        Integer totalPeopleNum = 0;
        Integer totalPeopleCount = 0;
        for (PeopleFlow flow : peopleFlowList) {
            Map map = new HashMap();
            int sum = setMapValue(year, month, flow, map);
            mapList.add(map);

            totalPeopleNum += flow.getValue();
            totalPeopleCount += sum;
        }
        Map total = new HashMap();
        total.put("name", name);
        total.put("peopleNum", totalPeopleNum);
        total.put("peopleCount", totalPeopleCount);
        mapList.add(0, total);

        return mapList;
    }

    private int setMapValue(Integer year, Integer month, PeopleFlow flow, Map map) {
        map.put("name", flow.getSceneName());
        map.put("peopleNum", flow.getValue());
        List<Analysis> analysisList = selectAnalysisByNameAndCategory(year, month, flow.getSceneName(), "运动频次");
        int sum = analysisList.stream().mapToInt(analysis -> {
            Integer count = getRunCountSum(analysis);
            return count;
        }).sum();
        map.put("peopleCount", sum);
        return sum;
    }

    public Integer getRunCountSum(Analysis analysis) {
        return analysis.getDictValue() * getAverageValue(analysis.getDictCode());
    }

    public Integer getAverageValue(String dictCode) {
        String res = dictCode.substring(0, dictCode.indexOf("次"));
        String[] split = res.split("-");
        int sum = Arrays.asList(split).stream().mapToInt(Integer::parseInt).sum();
        return sum / split.length;
    }

    private List<PeopleFlow> selectAllPeoPleFlowByDate(Integer year, Integer month) {
        Example example = new Example(PeopleFlow.class);
        example.createCriteria().andEqualTo("year", year).andEqualTo("month", month);
        return peopleFlowMapper.selectByExample(example);
    }

    public BaseResult selectGraph(Integer year, Integer month, String[] name, String category, Integer type) {
        year = year == null ? DateUtils.getYear(null) : year;
        month = month == null ? DateUtils.getMonth(null) - 1 : month;

        List<String> xArr = selectXArrAsc(year, month, name, category);
        if (category.equalsIgnoreCase("消费档次")) {
            String one = xArr.get(0);
            String two = xArr.get(1);
            xArr.set(0, two);
            xArr.set(1, one);
        }
        List<Map> list = new ArrayList<>();
        for (String code : name) {
            Map map = new HashMap();
            if ("全区".equalsIgnoreCase(code)) {
                List<Analysis> analyses = selectAllSceneAnalysisByCategory(year, month, category);
                List yArr = getYArr(xArr, analyses, category, type);
                map.put("name", code);
                map.put("data", yArr);

            } else {
                List<Analysis> analyses = selectAnalysisByNameAndCategory(year, month, code, category);
                List<Integer> yArr = getYArr(xArr, analyses, category, type);
                map.put("name", code);
                map.put("data", yArr);
            }
            list.add(map);
        }
        if (xArr.size() > 0 && (category.equalsIgnoreCase("性别") || category.equalsIgnoreCase("车产"))) {
            List<String> xList = Arrays.asList(name);
            Map yMap1 = new HashMap();
            Map yMap2 = new HashMap();
            List<String> yList1 = new ArrayList<>();
            List<String> yList2 = new ArrayList<>();
            for (Map map : list) {
                List<String> data = (List<String>) map.get("data");
                yList1.add(data.get(0));
                yList2.add(data.get(1));
            }
            yMap1.put("name", xArr.get(0));
            yMap1.put("data", yList1);
            yMap2.put("name", xArr.get(1));
            yMap2.put("data", yList2);

            List<Map> mapList = new ArrayList<>();
            mapList.add(yMap1);
            mapList.add(yMap2);
            return BaseResult.ok(Graph.builder().xArr(xList).yArr(mapList).build());
        }
        return BaseResult.ok(Graph.builder().xArr(xArr).yArr(list).build());
    }

    private List getYValue(List<String> xArr, List<Analysis> analyses, String category) {
        List yArr = new ArrayList<>();
        /*if (category.equalsIgnoreCase("兴趣偏好") || category.equalsIgnoreCase("行业分布")) {
            xArr = xArr.subList(0, 4);
            xArr.add("其他");
        }*/
        for (String x : xArr) {
            boolean b = true;
            if (!x.equalsIgnoreCase("其它")) {
                for (Analysis analysis : analyses) {
                    if (x.equalsIgnoreCase(analysis.getDictCode())) {
                        yArr.add(analysis.getDictValue());
                        b = false;
                        break;
                    }
                }
                if (b) {
                    yArr.add(0);
                }
            } else {
                int sum = 0;
                for (Analysis analysis : analyses) {
                    if (!xArr.contains(analysis.getDictCode())) {
                        sum += analysis.getDictValue();
                    }
                }
                yArr.add(sum);
            }
        }
        return yArr;
    }

    private List getYArr(List<String> xArr, List<Analysis> analyses, String category, Integer type) {
        List yArr = new ArrayList<>();
        /*if (category.equalsIgnoreCase("兴趣偏好") || category.equalsIgnoreCase("行业分布")) {
            xArr = xArr.subList(0, 4);
            xArr.add("其他");
        }*/
        for (String x : xArr) {
            boolean b = true;
            if (!x.equalsIgnoreCase("其它")) {
                for (Analysis analysis : analyses) {
                    if (x.equalsIgnoreCase(analysis.getDictCode())) {
                        if ((category.equalsIgnoreCase("性别") || category.equalsIgnoreCase("车产")) && type == null) {
                            yArr.add(analysis.getRate());
                        } else {
                            yArr.add(analysis.getDictValue());
                        }
                        b = false;
                        break;
                    }
                }
                if (b) {
                    yArr.add(0);
                }
            } else {
                int sum = 0;
                for (Analysis analysis : analyses) {
                    if (!xArr.contains(analysis.getDictCode())) {
                        sum += analysis.getDictValue();
                    }
                }
                yArr.add(sum);
            }
        }
        return yArr;
    }

    private List<String> selectXArrAsc(Integer year, Integer month, String[] name, String category) {
        if (Arrays.asList(name).contains("全区")) {
            return analysisMapper.selectXArr(year, month, null, category);
        } else {
            return analysisMapper.selectXArr(year, month, name, category);
        }
    }


    private List<Analysis> selectAllSceneAnalysisByCategory(Integer year, Integer month, String category) {
        return analysisMapper.selectAllSceneAnalysisByCategory(year, month, category);
    }

    private List<Analysis> selectAnalysisByNameAndCategory(Integer year, Integer month, String name, String category) {
        Example example = new Example(Analysis.class);
        example.createCriteria().andEqualTo("sceneName", name).andEqualTo("category", category)
                .andEqualTo("year", year).andEqualTo("month", month);
        return analysisMapper.selectByExample(example);
    }

    public BaseResult selectRunCountByTime(Integer year, Integer month, String[] names, Integer reqType) throws Exception {
        String startTime;
        String endTime;
        if (reqType == null) {
            startTime = year + "-" + month + "-01";
            endTime = year + "-" + (month + 1) + "-01";
        } else {
            if (reqType == 1) {
                startTime = DateUtils.getPastDate(null, 7);
                endTime = DateUtils.getFetureDate(null, 1);
            } else {
                startTime = DateUtils.getPastDate(null, 30);
                endTime = DateUtils.getFetureDate(null, 1);
            }
        }
        List<Map<String, Map<String, Integer>>> list = new ArrayList<>();
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Connection conn = DriverManager.getConnection("jdbc:sqlserver://" + dbUrl + ":1433;DatabaseName=IoTManagement_ModuleDb"
                , "sa", "1A2b3Cqwe123`");
        //新建一个查询
        Statement stmt = conn.createStatement();


        for (String sceneName : names) {
            Map result = new HashMap();
            List<String> ids = selectSceneIdByName(sceneName, year, month);
            if (ids.size() == 0) {
                return BaseResult.ok();
            }
            String sqlList = getSqlList(ids);
            //执行查询-->>返回一个结果集
            String sql = "SELECT * from TimesDistributions where cast(SceneId as varchar(36)) in " + sqlList + " and " +
                    "CreationTime " +
                    "in ( SELECT max(CreationTime) " +
                    "t FROM TimesDistributions group by  SceneId,convert(varchar(100),CreationTime,23) ) " +
                    "and CreationTime BETWEEN '" + startTime + "' and '" + endTime + "' ORDER BY CreationTime desc";
            System.out.println(sql);
            ResultSet rs = stmt.executeQuery(sql);    //括号里可以写相关的SQL语句，并把查询到的所有，放到一个rs集合里
            int count = 0;
            Map<String, Integer> map = new TreeMap<>();
            while (rs.next()) {//rs.next()返回的是一个boolean值，这是一个指针，表示查询表头部的的下一条数据，加载第二次就是头部的下一条的下一条，以此类推
                String timeSlot = rs.getString("TimeSlot");//这是查找数据库的id号
                if (StringUtils.isNotBlank(timeSlot) && !"null".equalsIgnoreCase(timeSlot)) {
                    JSONArray jsonArray = JSONArray.fromObject(timeSlot);
                    if (jsonArray.size() > 0) {
                        count++;
                        for (int i = 0; i < jsonArray.size(); i++) {
                            JSONObject jsonObject = jsonArray.getJSONObject(i);
                            String name = jsonObject.getString("name");
                            int value = jsonObject.getInt("value");
                            Integer sum = map.get(name);
                            if (sum == null) {
                                map.put(name, value);
                            } else {
                                map.put(name, sum + value);
                            }
                        }
                    }
                }
            }
            rs.close();
            result.put(sceneName, map);
            //RedisUtil.set(sceneName + year + month, result, 7 * 24 * 60 * 60L);
            list.add(result);
        }
        stmt.close();//这三行是关闭连接的意思，这非常重要，如果没写关闭连接
        conn.close();//程序多人打开或多人访问，就会出现卡顿，重启或奔溃


        Graph graph = convertGraph(list);
        return BaseResult.ok(graph);
    }

    private Graph convertGraph(List<Map<String, Map<String, Integer>>> list) {
        List<String> xArr = new ArrayList<>();
        List<Map> y = new ArrayList<>();
        for (Map<String, Map<String, Integer>> map : list) {
            for (Map.Entry<String, Map<String, Integer>> entry : map.entrySet()) {
                String key = entry.getKey();
                Map<String, Integer> value = entry.getValue();
                List<Integer> yArr = new ArrayList<>();
                for (Map.Entry<String, Integer> integerEntry : value.entrySet()) {
                    String key1 = integerEntry.getKey();
                    Integer value1 = integerEntry.getValue();
                    if (!xArr.contains(key1)) {
                        xArr.add(key1);
                    }
                    yArr.add(value1);
                }
                Map map1 = new HashMap();
                map1.put("name", key);
                map1.put("data", yArr);
                y.add(map1);
            }
        }
        return Graph.builder().xArr(xArr).yArr(y).build();
    }

    public String getSqlList(List<String> ids) {
        StringBuilder sb = new StringBuilder();
        sb.append(" (");
        for (String id : ids) {
            sb.append("'").append(id).append("',");
        }
        return sb.substring(0, sb.lastIndexOf(",")) + ")";
    }

    private List<String> selectSceneIdByName(String sceneName, Integer year, Integer month) {
        String deviceIds = getDeviceIdString(sceneName, year, month);
        String[] split = deviceIds.split(",");
        Example example = new Example(DeviceScene.class);
        example.createCriteria().andIn("deviceId", Arrays.asList(split));
        return deviceSceneMapper.selectByExample(example).stream().map(DeviceScene::getSceneId).collect(Collectors.toList());
    }

    private String getDeviceIdString(String sceneName, Integer year, Integer month) {
        if ("全区".equalsIgnoreCase(sceneName)) {
            Example example = new Example(PeopleFlow.class);
            example.createCriteria().andEqualTo("year", year).andEqualTo("month", month);
            List<String> deviceIdList = peopleFlowMapper.selectByExample(example).stream().map(PeopleFlow::getDeviceId).collect(Collectors.toList());
            return deviceIdList.stream().collect(Collectors.joining(","));
        } else {
            Example example = new Example(PeopleFlow.class);
            example.createCriteria().andEqualTo("sceneName", sceneName).andEqualTo("year", year).andEqualTo("month", month);
            return peopleFlowMapper.selectByExample(example).stream().map(PeopleFlow::getDeviceId).collect(Collectors.toList()).get(0);
        }

    }

    public JSONObject selectRunCount(String date) throws Exception {
        java.util.Date parse = new java.util.Date();
        if (StringUtils.isNotBlank(date)) {
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
            parse = format.parse(date);
        }
        if (StringUtils.isBlank(date) || DateUtils.isSameDate(parse, new java.util.Date())) {
            List<WifeDevice> list = selectAllWifiDeviceId();
            List<String> ids = list.stream().map(WifeDevice::getId).collect(Collectors.toList());
            return ShuWeiUtil.timeDistribution(ids);
        } else {
            String endTime = DateUtils.getFetureDate(parse, 1);
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection("jdbc:sqlserver://" + dbUrl + ":1433;DatabaseName" +
                            "=IoTManagement_ModuleDb"
                    , "sa", "1A2b3Cqwe123`");
            //新建一个查询
            Statement stmt = conn.createStatement();
            String sql = "SELECT * from TimesDistributions where  " +
                    "CreationTime " +
                    "in ( SELECT max(CreationTime) " +
                    "t FROM TimesDistributions group by  SceneId,convert(varchar(100),CreationTime,23) ) " +
                    "and CreationTime BETWEEN '" + date + "' and '" + endTime + "' ORDER BY CreationTime desc";
            System.out.println(sql);
            ResultSet rs = stmt.executeQuery(sql);    //括号里可以写相关的SQL语句，并把查询到的所有，放到一个rs集合里
            Map<String, Integer> map = new TreeMap<>();
            while (rs.next()) {//rs.next()返回的是一个boolean值，这是一个指针，表示查询表头部的的下一条数据，加载第二次就是头部的下一条的下一条，以此类推
                String timeSlot = rs.getString("TimeSlot");//这是查找数据库的id号
                if (StringUtils.isNotBlank(timeSlot) && !"null".equalsIgnoreCase(timeSlot)) {
                    JSONArray jsonArray = JSONArray.fromObject(timeSlot);
                    if (jsonArray.size() > 0) {
                        for (int i = 0; i < jsonArray.size(); i++) {
                            JSONObject jsonObject = jsonArray.getJSONObject(i);
                            String name = jsonObject.getString("name");
                            int value = jsonObject.getInt("value");
                            Integer sum = map.get(name);
                            if (sum == null) {
                                map.put(name, value);
                            } else {
                                map.put(name, sum + value);
                            }
                        }
                    }
                }
            }

            List<Map> list = new ArrayList<>();
            for (Map.Entry<String, Integer> entry : map.entrySet()) {
                Map data = new HashMap();
                String key = entry.getKey();
                Integer value = entry.getValue();
                data.put("name", key);
                data.put("value", value);
                list.add(data);
            }
            return JSONObject.fromObject(BaseResult.ok(list));
        }
    }

    private List<WifeDevice> selectAllWifiDeviceId() {
        List<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(2);
        Example example = new Example(WifeDevice.class);
        example.createCriteria().andIn("type", list);
        return wifeDeviceMapper.selectByExample(example);
    }

    public BaseResult selectRunNextDays(Integer day, String date) throws Exception {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date parse = format.parse(date);
        String startTime = DateUtils.getPastDate(parse, day);
        String endTime = DateUtils.getFetureDate(parse, 1);
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Connection conn = DriverManager.getConnection("jdbc:sqlserver://" + dbUrl + ":1433;DatabaseName" +
                        "=IoTManagement_ModuleDb"
                , "sa", "1A2b3Cqwe123`");
        //新建一个查询
        Statement stmt = conn.createStatement();
        Map result = new HashMap();
        List<String> ids = selectSceneIdByName("全区", null, null);
        if (ids.size() == 0) {
            return BaseResult.ok();
        }
        String sqlList = getSqlList(ids);
        //执行查询-->>返回一个结果集
        String sql = "SELECT * from TimesDistributions where cast(SceneId as varchar(36)) in " + sqlList + " and " +
                "CreationTime " +
                "in ( SELECT max(CreationTime) " +
                "t FROM TimesDistributions group by  SceneId,convert(varchar(100),CreationTime,23) ) " +
                "and CreationTime BETWEEN '" + startTime + "' and '" + endTime + "' ORDER BY CreationTime desc";
        System.out.println(sql);
        ResultSet rs = stmt.executeQuery(sql);
        List<Map> mapList = new ArrayList<>();//括号里可以写相关的SQL语句，并把查询到的所有，放到一个rs集合里
        while (rs.next()) {//rs.next()返回的是一个boolean值，这是一个指针，表示查询表头部的的下一条数据，加载第二次就是头部的下一条的下一条，以此类推
            String timeSlot = rs.getString("TimeSlot");//这是查找数据库的id号
            Date creationTime = rs.getDate("CreationTime");
            Map map = new HashMap();
            map.put("creationTime", creationTime);
            map.put("timeSlot", timeSlot);
            mapList.add(map);
        }
        List<Integer> yList = new ArrayList<>();
        for (int i = 0; i <= day; i++) {
            long l = DateUtils.formatDate(startTime).getTime() + (long) i * 24 * 60 * 60 * 1000;
            if (l < DateUtils.formatDate(endTime).getTime()) {
                int sum = 0;
                for (Map map : mapList) {
                    Object timeSlot1 = map.get("timeSlot");
                    if (timeSlot1 == null) {
                        continue;
                    }
                    String timeSlot = timeSlot1.toString();//这是查找数据库的id号
                    Date creationTime = (Date) map.get("creationTime");
                    if (DateUtils.formatDate(new Date(l)).equalsIgnoreCase(DateUtils.formatDate(creationTime))) {
                        if (StringUtils.isNotBlank(timeSlot) && !"null".equalsIgnoreCase(timeSlot)) {
                            JSONArray jsonArray = JSONArray.fromObject(timeSlot);
                            if (jsonArray.size() > 0) {
                                JSONObject jsonObject = jsonArray.getJSONObject(jsonArray.size() - 1);
                                int value = jsonObject.getInt("value");
                                sum += value;
                            }
                        }
                    }
                }
                yList.add(sum);
            }
        }
        rs.close();
        stmt.close();//这三行是关闭连接的意思，这非常重要，如果没写关闭连接
        conn.close();//程序多人打开或多人访问，就会出现卡顿，重启或奔溃
        return BaseResult.ok(yList);
    }

    public BaseResult selectPeopleAnalysis() throws ParseException {

        Map map = new HashMap();
        int year = DateUtils.getYear(null);
        int month = DateUtils.getMonth(null);

        String res = OkHttpUtils.synGet(appUrl + "?date=" + year + "-" + month + "-1", null);
        Integer count = 0;
        if (StringUtils.isNotBlank(res)) {
            count = (Integer) JSONObject.fromObject(res).get("data");
        }
        int total = selectAllPeoPleFlowByDate(year, month - 1).stream().mapToInt(PeopleFlow::getValue).sum();
        Map people = new HashMap();
        people.put("total", count + total);


        String lastRes = OkHttpUtils.synGet(appUrl + "?date=" + year + "-" + (month - 1) + "-1", null);
        Integer lastCount = 0;
        if (StringUtils.isNotBlank(lastRes)) {
            lastCount = (Integer) JSONObject.fromObject(lastRes).get("data");
        }
        int laseMonthTotal = selectAllPeoPleFlowByDate(year, month - 2).stream().mapToInt(PeopleFlow::getValue).sum();
        people.put("difference", String.format("%.4f",
                (double) (count + total - lastCount - laseMonthTotal) / (laseMonthTotal + lastCount)));


        int deviceNumNow = selectDeviceNum(year, month);
        int deviceNumLast = selectDeviceNum(year, month - 1);
        Map device = new HashMap();
        device.put("total", deviceNumNow);
        device.put("difference", String.format("%.4f", (double) (deviceNumNow - deviceNumLast) / deviceNumLast));

        Map platform = new HashMap();
        platform.put("total", 3);
        platform.put("difference", 0);

        map.put("people", people);
        map.put("device", device);
        map.put("platform", platform);

        return BaseResult.ok(map);
    }

    private int selectDeviceNum(int year, int month) throws ParseException {
        String date = year + "-" + (month + 1) + "-" + 1;
        java.util.Date now = DateUtils.formatDate(date);
        Example example = new Example(WifeDevice.class);
        example.createCriteria().andLessThan("createTime", now);
        return wifeDeviceMapper.selectCountByExample(example);
    }

    public BaseResult selectLocationAnalysis() {
        int year = DateUtils.getYear(null);
        int month = DateUtils.getMonth(null) - 1;

        Map map = analysisMapper.selectLocationAnalysis(year, month);

        return BaseResult.ok(map);
    }

    public BaseResult selectRunTypeAnalysis(Integer type) throws Exception {
        int year = DateUtils.getYear(null);
        int month = DateUtils.getMonth(null) - 1;
        List<String> ids = selectSceneIdByName("全区", year, month);
        if (ids.size() == 0) {
            return BaseResult.ok();
        }
        String sqlList = getSqlList(ids);

        String startTime;
        String endTime;
        if (type == null) {
            type = 1;
        }
        if (type == 0) {
            startTime = year + "-" + month + "-" + 1;
            endTime = year + "-" + (month + 1) + "-" + 1;
        } else if (type == 1) {
            int day = DateUtils.getDay(null);
            startTime = year + "-" + (month + 1) + "-" + day;
            endTime = year + "-" + (month + 1) + "-" + (day + 1);
        } else if (type == 2) {
            startTime = year + "-" + (month + 1) + "-" + 1;
            endTime = year + "-" + (month + 2) + "-" + 1;
        } else {
            startTime = year + "-" + 1 + "-" + 1;
            endTime = (year + 1) + "-" + 1 + "-" + 1;
        }

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Connection conn = DriverManager.getConnection("jdbc:sqlserver://" + dbUrl + ":1433;DatabaseName" +
                        "=IoTManagement_ModuleDb"
                , "sa", "1A2b3Cqwe123`");
        //新建一个查询
        Statement stmt = conn.createStatement();
        //执行查询-->>返回一个结果集
        String sql = "SELECT * from CrowdAttributes where cast(SceneId as varchar(36)) in " + sqlList +
                "and SportPreference not in ('null','[]')" +
                "and CreationTime BETWEEN '" + startTime + "' and '" + endTime + "' ORDER BY CreationTime desc";
        System.out.println(sql);
        ResultSet rs = stmt.executeQuery(sql);    //括号里可以写相关的SQL语句，并把查询到的所有，放到一个rs集合里
        int count = 0;
        Map<String, Double> map = new TreeMap<>();
        while (rs.next()) {//rs.next()返回的是一个boolean值，这是一个指针，表示查询表头部的的下一条数据，加载第二次就是头部的下一条的下一条，以此类推
            String SportPreference = rs.getString("SportPreference");//这是查找数据库的id号
            if (StringUtils.isNotBlank(SportPreference) && !"null".equalsIgnoreCase(SportPreference)) {
                JSONArray jsonArray = JSONArray.fromObject(SportPreference);
                if (jsonArray.size() > 0) {
                    count++;
                    for (int i = 0; i < jsonArray.size(); i++) {
                        JSONObject jsonObject = jsonArray.getJSONObject(i);
                        String name = jsonObject.getString("name");
                        double value = Double.parseDouble(String.format("%.4f", jsonObject.getDouble("rate")));
                        //Double value = jsonObject.getDouble("rate");
                        Double sum = map.get(name);
                        if (sum == null) {
                            map.put(name, value);
                        } else {
                            double v = sum + value;
                            //double parseDouble = Double.parseDouble(String.format("%.4f", v));
                            map.put(name, v);
                        }
                    }
                }
            }
        }
        rs.close();
        stmt.close();//这三行是关闭连接的意思，这非常重要，如果没写关闭连接
        conn.close();//程序多人打开或多人访问，就会出现卡顿，重启或奔溃

        Map res = new HashMap();
        List<String> xArr = new ArrayList<>();
        List<String> yArr = new ArrayList<>();
        for (Map.Entry<String, Double> entry : map.entrySet()) {
            String key = entry.getKey();
            String value = String.format("%.4f", entry.getValue() / count);

            xArr.add(key);
            yArr.add(value);
            res.put("xArr", xArr);
            res.put("yArr", yArr);
        }
        return BaseResult.ok(res);
    }

    public BaseResult selectRunTypeAnalysisNow() {
        List<WifeDevice> list = selectAllWifiDeviceId();
        List<String> ids = list.stream().map(WifeDevice::getId).collect(Collectors.toList());
        JSONObject jsonObject = ShuWeiUtil.crowdAttribute(ids);
        return BaseResult.ok(jsonObject.getJSONArray("sportPreferences"));
    }


    public BaseResult selectCityAnalysis() {
        int year = DateUtils.getYear(null);
        int month = DateUtils.getMonth(null) - 1;
        List<Map> map = analysisMapper.selectCityAnalysis(year, month);
        return BaseResult.ok(map);
    }

    public BaseResult selectRunCountYMD(Integer type, String time) throws Exception {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Connection conn = DriverManager.getConnection("jdbc:sqlserver://" + dbUrl + ":1433;DatabaseName" +
                        "=IoTManagement_ModuleDb"
                , "sa", "1A2b3Cqwe123`");
        //新建一个查询
        Statement stmt = conn.createStatement();
        String startTime;
        String endTime;
        if (type == 1) {
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
            startTime = time;
            endTime = DateUtils.getFetureDate(format.parse(time), 1);
        } else if (type == 2) {
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
            startTime = time + "-1";
            java.util.Date parse = format.parse(time);
            int month = DateUtils.getMonth(parse);
            endTime = DateUtils.getYear(parse) + "-" + (month + 1) + "-1";
        } else {
            startTime = time + "-1-1";
            endTime = (Integer.parseInt(time) + 1) + "-1-1";
        }
        String sql = "SELECT * from TimesDistributions where  " +
                "CreationTime " +
                "in ( SELECT max(CreationTime) " +
                "t FROM TimesDistributions group by  SceneId,convert(varchar(100),CreationTime,23) ) " +
                "and CreationTime BETWEEN '" + startTime + "' and '" + endTime + "' ORDER BY CreationTime desc";
        System.out.println(sql);
        ResultSet rs = stmt.executeQuery(sql);    //括号里可以写相关的SQL语句，并把查询到的所有，放到一个rs集合里
        Map<String, Integer> map = new TreeMap<>();
        Integer sum = 0;
        while (rs.next()) {//rs.next()返回的是一个boolean值，这是一个指针，表示查询表头部的的下一条数据，加载第二次就是头部的下一条的下一条，以此类推
            String timeSlot = rs.getString("TimeSlot");//这是查找数据库的id号
            if (StringUtils.isNotBlank(timeSlot) && !"null".equalsIgnoreCase(timeSlot)) {
                JSONArray jsonArray = JSONArray.fromObject(timeSlot);
                if (jsonArray.size() > 0) {
                    for (int i = 0; i < jsonArray.size(); i++) {
                        JSONObject jsonObject = jsonArray.getJSONObject(i);
                        //String name = jsonObject.getString("name");
                        int value = jsonObject.getInt("value");
                        sum += value;
                    }
                }
            }
        }

        return BaseResult.ok(sum);
    }

    public BaseResult selectCityLocal(Integer year, Integer month, String[] name) {
        List<String> xList = new ArrayList<>();
        xList.add("本地");
        xList.add("本市");

        /*List<Map<String, String>> mapsLocal = analysisMapper.selectTotalGroupByName(year, month, name, "居住-本地");
        List<Map<String, String>> mapsCityLocal = analysisMapper.selectTotalGroupByName(year, month, name, "居住-本市");
        mapsLocal.addAll(mapsCityLocal);*/

        List<Map> yList = new ArrayList<>();
        for (String s : name) {
            List<Integer> list = new ArrayList<>();
            Map<String, String> res1 = analysisMapper.selectAllGroupByName(year, month, s, "居住-本地");
            Map<String, String> res2 = analysisMapper.selectAllGroupByName(year, month, s, "居住-本市");
            list.add(Integer.parseInt(res1.get("data")));
            list.add(Integer.parseInt(res2.get("data")));
            Map map = new HashMap();
            map.put("name", s);
            map.put("data", list);
            yList.add(map);
        }


        return BaseResult.ok(Graph.builder().xArr(xList).yArr(yList).build());
    }

    public BaseResult selectSuburbsGraph(Integer year, Integer month, String[] name) {
        List<String> xList = new ArrayList<>();
        List<Map> yList = new ArrayList<>();
        List<Map> one = new ArrayList<>();
        List<Map> two = new ArrayList<>();
        List<Map> three = new ArrayList<>();
        for (String s : name) {
            xList.add(s);
            List<Analysis> analyses;
            if ("全区".equalsIgnoreCase(s)) {
                analyses = analysisMapper.selectALLAreaTopThreeAnalysis(year, month, s);
            } else {
                analyses = analysisMapper.selectTopThreeAnalysis(year, month, s);
            }
            one.add(analyses.size() > 0 ? getMap(analyses.get(0)) : null);
            two.add(analyses.size() > 1 ? getMap(analyses.get(1)) : null);
            three.add(analyses.size() > 2 ? getMap(analyses.get(2)) : null);
        }
        Map mapOne = new HashMap();
        mapOne.put("name", "one");
        mapOne.put("data", one);
        Map mapTwo = new HashMap();
        mapTwo.put("name", "two");
        mapTwo.put("data", two);
        Map mapThree = new HashMap();
        mapThree.put("name", "three");
        mapThree.put("data", three);

        yList.add(mapOne);
        yList.add(mapTwo);
        yList.add(mapThree);

        return BaseResult.ok(Graph.builder().xArr(xList).yArr(yList).build());
    }

    private Map getMap(Analysis analysis) {
        Map map = new HashMap();
        map.put("name", analysis.getDictCode());
        map.put("value", analysis.getDictValue());
        return map;
    }
}
